package be.webchess.www.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import be.webchess.www.model.ChessPiece;
import be.webchess.www.model.User;

public class ChessPieceDAO extends GenericDAO<ChessPiece> {

	//ChessBoardDAO		boardDAO;
	
	public ChessPieceDAO(Connection c) {
		super(c);
		
		//boardDAO = new ChessBoardDAO(c);
	}

	@Override
	public ChessPiece findByID(Long id) throws SQLException {

		ChessPiece piece = null;
		
		StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append("p.id, ");
		query.append("p.id_board, ");
		query.append("p.flags, ");
		query.append("p.location, ");
		query.append("p.type, ");
		query.append("p.color, ");
		query.append("p.movements, ");
		query.append("p.init_movements ");
		query.append("FROM piece p");
//		query.append("INNER JOIN piece p on p.id=b.idUser ");
		query.append("WHERE p.id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		stmt.setLong(1, id);

		ResultSet result = stmt.executeQuery();
		if(result.next()) {
			piece = new ChessPiece();
			buildObject(result, piece);
		}
		
		return piece;
	}

	@Override
	public ChessPiece insert(ChessPiece o) throws SQLException {
		StringBuffer query = new StringBuffer();
		query.append("INSERT INTO piece(");
		query.append("flags, ");
		query.append("location, ");
		query.append("type, ");
		query.append("id_board, ");
		query.append("movements, ");
		query.append("init_movements, ");
		query.append("color ");
		query.append(") VALUES (?,?,?,?,?,?,?)");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
//		query.append("WHERE u.id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString(),Statement.RETURN_GENERATED_KEYS);
		stmt.setInt(1, o.getFlags());
		stmt.setString(2, o.getLocation());
		stmt.setString(3, o.getType().toString());
		stmt.setLong(4, o.getBoard().getId());
		stmt.setString(5, o.getMovements());
		stmt.setString(6, o.getInit_movements());
		stmt.setString(7, o.getColor());
		
		stmt.executeUpdate();

		ResultSet result = stmt.getGeneratedKeys();
		
		if(result.next()) {
			o.setId(result.getLong(1));
		}
		
		return o;
	}

	@Override
	public ChessPiece update(ChessPiece o) throws SQLException {

		StringBuffer query = new StringBuffer();
		query.append("UPDATE piece SET ");
		query.append("flags=?, ");
		query.append("location=?, ");
		query.append("type=?, ");
		query.append("color=?, ");
		query.append("id_board=?, ");
		query.append("movements=?, ");
		query.append("init_movements=? ");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
		query.append("WHERE id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		stmt.setInt(1, o.getFlags());
		stmt.setString(2, o.getLocation());
		stmt.setString(3, o.getType().toString());
		stmt.setString(4, o.getColor());
		stmt.setLong(5, o.getBoard().getId());
		stmt.setString(6, o.getMovements());
		stmt.setString(7, o.getInit_movements());
		stmt.setLong(8, o.getId());
		
		stmt.executeUpdate();
		
		return o;
	}

	@Override
	public void delete(ChessPiece o) throws SQLException {

		StringBuffer query = new StringBuffer();
		query.append("DELETE FROM piece ");
		query.append("WHERE id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		stmt.setLong(1, o.getId());
		stmt.executeUpdate();
	}

	@Override
	public List<ChessPiece> findAll() throws SQLException {

		LinkedList<ChessPiece> results = new LinkedList<ChessPiece>();
		
		StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append("p.id, ");
		query.append("p.id_board, ");
		query.append("p.flags, ");
		query.append("p.location, ");
		query.append("p.type, ");
		query.append("p.color, ");
		query.append("p.movements, ");
		query.append("p.init_movements ");
		query.append("FROM piece p ");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
//		query.append("WHERE u.id=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		
		ResultSet result = stmt.executeQuery();
		while(result.next()) {
			ChessPiece piece = new ChessPiece();
			buildObject(result, piece);
			results.add(piece);
		}
		
		return results;		
	}
	
	public List<ChessPiece> findByBoardID( Long id ) throws SQLException {

		LinkedList<ChessPiece> results = new LinkedList<ChessPiece>();
		
		StringBuffer query = new StringBuffer();
		query.append("SELECT ");
		query.append("p.id, ");
		query.append("p.id_board, ");
		query.append("p.flags, ");
		query.append("p.location, ");
		query.append("p.type, ");
		query.append("p.color, ");
		query.append("p.movements, ");
		query.append("p.init_movements ");
		query.append("FROM piece p ");
//		query.append("INNER JOIN users u on u.idUsers=a.idUser ");
		query.append("WHERE p.id_board=?");
		
		PreparedStatement stmt = con.prepareStatement(query.toString());
		
		stmt.setLong(1, id);
		
		ResultSet result = stmt.executeQuery();
		while(result.next()) {
			ChessPiece piece = new ChessPiece();
			buildObject(result, piece);
			results.add(piece);
		}
		
		return results;		
	}
	
	private void buildObject(ResultSet result, ChessPiece piece) throws SQLException{
		
		int		col = 1;		
		
		piece.setId(result.getLong(col++));
		// id_board
		//piece.setBoard(boardDAO.findByID(result.getLong(col++)));
		col++;
		//
		piece.setFlags(result.getInt(col++));		
		piece.setLocation(result.getString(col++));		
		piece.setType( ChessPiece.PieceType.valueOf(result.getString(col++)));
		piece.setColor(result.getString(col++));
		piece.setMovements(result.getString(col++));
		piece.setInit_movements(result.getString(col++));	
				
	}	
}
